package cn.vonce.sqlbean.example.controller;

import cn.vonce.sql.bean.Select;
import cn.vonce.sql.define.SqlFun;
import cn.vonce.sql.page.PageHelper;
import cn.vonce.sql.page.ResultData;
import cn.vonce.sqlbean.example.common.Result;
import cn.vonce.sqlbean.example.enums.UserStatus;
import cn.vonce.sqlbean.example.model.entity.Essay;
import cn.vonce.sqlbean.example.model.entity.User;
import cn.vonce.sqlbean.example.model.entity.sql.Essay$;
import cn.vonce.sqlbean.example.model.vo.*;
import cn.vonce.sqlbean.example.service.EssayService;
import cn.vonce.sqlbean.example.service.UserService;
import cn.vonce.sql.helper.Cond;
import cn.vonce.sql.helper.Wrapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

/**
 * Select 控制器
 *
 * @author Jovi
 * @version 1.0
 * @email imjovi@qq.com
 * @date 2021-12-16 20:01:12
 */
@RequestMapping
@RestController
public class SelectController {

    @Autowired
    private UserService userService;

    @Autowired
    private EssayService essayService;

    /**
     * 统计全部
     *
     * @return
     */
    @GetMapping("count1")
    public Result count1() {
        int count = essayService.count();
        return Result.success(count);
    }

    /**
     * 根据条件统计
     *
     * @return
     */
    @GetMapping("count2")
    public Result count2() {
        int count = essayService.countBy(Wrapper.where(Cond.eq(SqlFun.date_format(Essay::getCreateTime, "%Y-%m-%d"), "2023-01-01")));
        return Result.success(count);
    }

    /**
     * 根据条件统计
     *
     * @return
     */
    @GetMapping("count3")
    public Result count3() {
        int count = essayService.countBy("& = ?", Essay$.creator, "Jovi");
        return Result.success(count);
    }

    /**
     * 根据id查询
     *
     * @param id
     * @return
     */
    @GetMapping("getById")
    public Result getById(Long id) {
        User user = userService.selectById(id);
        return Result.success(user);
    }

    /**
     * 查询全部
     *
     * @return
     */
    @GetMapping("getAll")
    public Result getAll() {
        List<User> userList = userService.select();
        return Result.success(userList);
    }

    /**
     * 查询全部 返回Map列表
     *
     * @return
     */
    @GetMapping("getMapList")
    public Result getMapList() {
        Select select = new Select();
        List<Map<String, Object>> sysUserList = userService.selectMapList(select);
        return Result.success(sysUserList);
    }

    /**
     * 分组统计 返回Map列表
     *
     * @return
     */
    @GetMapping("getMapList2")
    public Result getMapList2() {
        Select select = new Select();
        select.column(SqlFun.count(User::getId), "count").column(SqlFun.avg(User::getAge));
        select.where().gt(SqlFun.date_format(User::getCreateTime, "%Y-%m-%d"), "2024-06-24");
        select.groupBy(User::getGender);
        select.orderByDesc("count");
        List<Map<String, Object>> mapList = userService.selectMapList(select);
        return Result.success(mapList);
    }

    /**
     * 根据性别年龄查询
     *
     * @param beginAge
     * @param endAge
     * @param gender
     * @return
     */
    @GetMapping("getByAgeAndGender")
    public Result getByAgeAndGender(Integer beginAge, Integer endAge, Integer gender) {
        //方式一
        List<User> userList = userService.selectBy(Wrapper.where(Cond.gtEq(User::getAge, beginAge)).and(Cond.ltEq(User::getAge, endAge)).and(Cond.eq(User::getGender, gender)));
        //方式二更强大，支持指定查询字段，多种条件及分页分组等
        userList = userService.select(new Select().where().gtEq(User::getAge, beginAge).and().ltEq(User::getAge, endAge).and().eq(User::getGender, gender).back());
        return Result.success(userList);
    }

    /**
     * 查询指定你想要返回的VO对象
     *
     * @return
     */
    @GetMapping("getVOList")
    public Result getVOList() {
        Select select = new Select();
        //指定你要查询的字段，如果vo对象中的字段名称与表中字段一致的话也可以不指定，如果存在字段不一致则在查询该字段时需要指定别名
        select.column(User::getId).column(User::getUserName).column(User::getNickName).column(User::getMobilePhone, "mobile");
        select.where(Wrapper.where(Cond.eq(User::getStatus, UserStatus.NORMAL)));
        List<UserVO> userVOList = userService.select(UserVO.class, select);
        return Result.success(userVOList);
    }

    /**
     * 查询返回你想要的某个字段列表（比如只想要返回用户的手机号列表）
     *
     * @return
     */
    @GetMapping("getMobileList")
    public Result getMobileList() {
        Select select = new Select();
        select.column(User::getMobilePhone);
        List<String> mobileList = userService.select(String.class, select);
        return Result.success(mobileList);
    }


    /**
     * 查询文章列表，文章对象包含用户昵称
     *
     * @return
     */
    @GetMapping("getEssayList")
    public Result getEssayList() {
        Select select = new Select();
        List<EssayVO2> essayVOList = essayService.select(EssayVO2.class, select);
        return Result.success(essayVOList);
    }

    /**
     * 查询文章列表，文章对象包含用户对象
     *
     * @return
     */
    @GetMapping("getEssayUserList")
    public Result getEssayUserList() {
        Select select = new Select();
        List<EssayUserVO> essayUserVOList = essayService.select(EssayUserVO.class, select);
        return Result.success(essayUserVOList);
    }

    /**
     * 查询文章列表，文章对象包含用户部分信息
     *
     * @return
     */
    @GetMapping("getEssayUserList2")
    public Result getEssayUserList2() {
        Select select = new Select();
        select.column(Essay::getClass);
        select.column(User::getUserName).column(User::getNickName).column(User::getMobilePhone);
        select.leftJoin(User.class).on(User::getId, Essay::getUserId);
        List<EssayUserVO2> essayUserVOList = essayService.select(EssayUserVO2.class, select);
        return Result.success(essayUserVOList);
    }

    /**
     * 分页查询文章列表
     *
     * @param pageNum
     * @param pageSize
     * @return
     */
    @GetMapping("page1")
    public Result page1(Integer pageNum, Integer pageSize) {
        Select select = new Select();
        select.column(Essay::getId).column(Essay::getTitle).column(Essay::getContent).column(Essay::getUserId);
        select.where(Wrapper.where(Cond.eq(Essay::getDeleted, false)));
        PageHelper pageHelper = new PageHelper(pageNum, pageSize);
        ResultData<Essay> resultData = essayService.paging(select, pageHelper);
        return Result.success(resultData);
    }

    /**
     * 分页查询文章列表(包含用户对象)
     *
     * @param pageNum
     * @param pageSize
     * @return
     */
    @GetMapping("page2")
    public Result page2(Integer pageNum, Integer pageSize) {
        Select select = new Select();
        PageHelper pageHelper = new PageHelper(pageNum, pageSize);
        ResultData<EssayUserVO> resultData = essayService.paging(EssayUserVO.class, select, pageHelper);
        return Result.success(resultData);
    }

}
